/**
 * 
 */
package vn.edu.fpt.xml.hrm.model;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import javax.xml.datatype.DatatypeConfigurationException;

import vn.edu.fpt.xml.hrm.common.AppConstant;
import vn.edu.fpt.xml.hrm.common.CheckUtil;
import vn.edu.fpt.xml.hrm.common.FormatUtil;
import vn.edu.fpt.xml.hrm.pojo.Activities;
import vn.edu.fpt.xml.hrm.pojo.Activity;
import vn.edu.fpt.xml.hrm.pojo.Project;

/**
 * @author Yoshimi
 * 
 */
public class ActivityBean {

    private String dbms;
    private String serverName;
    private String port;
    private String dbName;
    private boolean integratedSecurity;
    private String username;
    private String password;

    /**
     *
     */
    public ActivityBean() {
        dbms = AppConstant.DB_DBMS;
        serverName = AppConstant.DB_SERVER_NAME;
        port = AppConstant.DB_PORT;
        dbName = AppConstant.DB_NAME;
        integratedSecurity = AppConstant.DB_INTEGRATED_SECURITY;
        username = AppConstant.DB_USERNAME;
        password = AppConstant.DB_PASSWORD;
    }

    /**
     * @return the dbms
     */
    protected String getDbms() {
        return dbms;
    }

    /**
     * @param dbms the dbms to set
     */
    protected void setDbms(String dbms) {
        this.dbms = dbms;
    }

    /**
     * @return the serverName
     */
    protected String getServerName() {
        return serverName;
    }

    /**
     * @param serverName the serverName to set
     */
    protected void setServerName(String serverName) {
        this.serverName = serverName;
    }

    /**
     * @return the port
     */
    protected String getPort() {
        return port;
    }

    /**
     * @param port the port to set
     */
    protected void setPort(String port) {
        this.port = port;
    }

    /**
     * @return the dbName
     */
    protected String getDbName() {
        return dbName;
    }

    /**
     * @param dbName the dbName to set
     */
    protected void setDbName(String dbName) {
        this.dbName = dbName;
    }

    /**
     * @return the integratedSecurity
     */
    protected boolean isIntegratedSecurity() {
        return integratedSecurity;
    }

    /**
     * @param integratedSecurity the integratedSecurity to set
     */
    protected void setIntegratedSecurity(boolean integratedSecurity) {
        this.integratedSecurity = integratedSecurity;
    }

    /**
     * @return the username
     */
    protected String getUsername() {
        return username;
    }

    /**
     * @param username the username to set
     */
    protected void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the password
     */
    protected String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    protected void setPassword(String password) {
        this.password = password;
    }

    private Connection connect() throws ClassNotFoundException, SQLException, NamingException {
        Context ctx = new InitialContext();
        Context envCtx = (Context) ctx.lookup("java:comp/env");
        DataSource ds = (DataSource) envCtx.lookup("DBCon");
        Connection con = ds.getConnection();
        return con;
    }

    /**
     * @param fromDate
     * @param toDate
     * @param projectName
     * @param proList
     * @param employeeName
     * @param status
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     * @throws DatatypeConfigurationException
     * @throws NamingException
     */
    public List<Activity> getApproveList(Date fromDate, Date toDate,
            String projectName, List<Project> proList, String employeeName, String status) throws SQLException, ClassNotFoundException, DatatypeConfigurationException, NamingException {
        List<Activity> result = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql = null;
        int paramIndex = 0;
        Activity activity = null;
        StringBuilder builder = null;
        int size = 0;
        if (proList != null && !proList.isEmpty()) {
            try {
                connection = connect();
                // 2. create query
                // 2.1. create the query
                sql = "SELECT DISTINCT d.DailyReportID AS id"
                        + ", d.EmployeeID AS empId"
                        + ", d.ProjectID AS proId"
                        + ", d.Date AS date"
                        + ", d.Description AS description"
                        + ", d.Duration AS duration"
                        + ", d.Status AS status"
                        + ", p.ProjectName AS proName"
                        + ", e.EmployeeName AS empName "
                        + "FROM DailyReport AS d "
                        + "LEFT JOIN Project AS p ON d.ProjectID = p.ProjectID "
                        + "LEFT JOIN Employee AS e ON d.EmployeeID = e.EmployeeID";
                // if a parameter is valid (not null, zero, etc), add the
                // corresponding search constraint to the query
                paramIndex = 1;
                if (fromDate != null) {
                    sql += " WHERE d.Date >= ?";
                    paramIndex++;
                }
                if (toDate != null) {
                    if (paramIndex > 1) {
                        sql += " AND d.Date <= ?";
                    } else {
                        sql += " WHERE d.Date <= ?";
                    }
                    paramIndex++;
                }
                if (!CheckUtil.isBlankOrNull(projectName)) {
                    if (paramIndex > 1) {
                        sql += " AND p.ProjectName = ?";
                    } else {
                        sql += " WHERE p.ProjectName = ?";
                    }
                    paramIndex++;
                } else {
                    builder = new StringBuilder();
                    size = proList.size();
                    for (int i = 0; i < size; i++) {
                        builder.append("?, ");
                    }
                    builder.delete(builder.lastIndexOf(", "), builder.length());
                    if (paramIndex > 1) {
                        sql += " AND d.ProjectID IN (" + builder.toString() + ")";
                    } else {
                        sql += " WHERE d.ProjectID IN (" + builder.toString() + ")";
                    }
                    paramIndex += size;
                }
                if (!CheckUtil.isBlankOrNull(employeeName)) {
                    if (paramIndex > 1) {
                        sql += " AND e.EmployeeName = ?";
                    } else {
                        sql += " WHERE e.EmployeeName = ?";
                    }
                    paramIndex++;
                }
                if (!CheckUtil.isBlankOrNull(status)) {
                    if (paramIndex > 1) {
                        sql += " AND d.Status = ?";
                    } else {
                        sql += " WHERE d.Status = ?";
                    }
                    paramIndex++;
                }
                // 2.2. turn the query to a preparedStatement
                statement = connection.prepareStatement(sql);
                // 2.3. add parameters to the statement
                paramIndex = 1;
                if (fromDate != null) {
                    statement.setDate(paramIndex, FormatUtil.dateToDate(fromDate));
                    paramIndex++;
                }
                if (toDate != null) {
                    statement.setDate(paramIndex, FormatUtil.dateToDate(toDate));
                    paramIndex++;
                }
                if (!CheckUtil.isBlankOrNull(projectName)) {
                    statement.setString(paramIndex, projectName);
                    paramIndex++;
                } else {
                    for (Project pro : proList) {
                        statement.setInt(paramIndex, pro.getId().intValue());
                        paramIndex++;
                    }
                }
                if (!CheckUtil.isBlankOrNull(employeeName)) {
                    statement.setString(paramIndex, employeeName);
                    paramIndex++;
                }
                if (!CheckUtil.isBlankOrNull(status)) {
                    statement.setString(paramIndex, status);
                    paramIndex++;
                }

                // 3. execute query
                resultSet = statement.executeQuery();

                // 4. read result
                result = new ArrayList<Activity>();
                while (resultSet.next()) {
                    activity = new Activity();
                    activity.setId(BigInteger.valueOf(resultSet.getInt("id")));
                    activity.setDate(FormatUtil.dateToCalendar(resultSet.getDate("date")));
                    activity.setDescription(resultSet.getString("description"));
                    activity.setStatus(resultSet.getString("status"));
                    activity.setProject(resultSet.getString("proName"));
                    activity.setEmployee(resultSet.getString("empName"));
                    activity.setDuration(resultSet.getDouble("duration"));
                    result.add(activity);
                }
            } finally {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            }
        }
        return result;
    }

    public int updateStatus(int[] ids, String status) throws SQLException, ClassNotFoundException, NamingException {
        int result = 0;
        Connection connection = null;
        String sql = null;
        int size = 0;
        StringBuilder inClause = null;
        PreparedStatement statement = null;
        int paramIndex = 0;
        if (ids != null && ids.length > 0) {
            try {
                connection = connect();
                // 2. create query
                // 2.1. create the query
                size = ids.length;
                inClause = new StringBuilder();
                for (int i = 0; i < size; i++) {
                    inClause.append("?, ");
                }
                inClause.delete(inClause.lastIndexOf(", "),
                        inClause.length());
                sql = "UPDATE \"DailyReport\" SET \"Status\" = ? WHERE \"DailyReportID\" IN ("
                        + inClause.toString() + ")";
                // 2.2. turn the query to a preparedStatement
                statement = connection.prepareStatement(sql);
                // 2.3. add parameters to the statement
                paramIndex = 1;
                statement.setString(paramIndex, status);
                paramIndex++;
                for (int i = 0; i < size; i++) {
                    statement.setInt(paramIndex, ids[i]);
                    paramIndex++;
                }

                // 3. execute query
                result = statement.executeUpdate();
            } finally {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            }
        }
        return result;
    }

    public boolean insertNewActivities(Activities activities) {
        Connection connection = null;
        PreparedStatement stm = null;
        try {

            connection = connect();
            String sql2 = "SELECT * FROM Project";
            stm = connection.prepareStatement(sql2);
            ResultSet rs = stm.executeQuery();

            for (int i = 0; i < activities.getActivity().size(); i++) {
                Activity item = activities.getActivity().get(i);
                while (rs.next()) {
                    String name = rs.getString("ProjectName");
                    if (name.equals(item.getProject())) {
                        int id = rs.getInt("ProjectID");

                        String sql = "INSERT INTO DailyReport"
                                + "(EmployeeID,Date,Description,ProjectID,Duration,Status) "
                                + "VALUES(?,?,?,?,?,?)";

                        stm = connection.prepareStatement(sql);
                        Date tmp = item.getDate().toGregorianCalendar().getTime();
                        SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
                        StringBuilder date = new StringBuilder(formatter.format(tmp));

                        stm.setString(1, item.getEmployee());
                        stm.setString(2, date.toString());
                        stm.setString(3, item.getDescription());
                        stm.setInt(4, id);
                        stm.setDouble(5, item.getDuration());
                        stm.setString(6, "unapproved");
                        stm.executeUpdate();
                    }
                }
                stm.close();
                connection.close();
            }
            return true;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ActivityBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(ActivityBean.class.getName()).log(Level.SEVERE, null, ex);
        } catch (NamingException ex) {
            Logger.getLogger(ActivityBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
}
